package dao;

import db.DbUtil;
import entity.New;

import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 蒋银辉
 * @version 1.0
 */
public class NewDao {

    public Integer total_news(String condition, List<Object> parmas) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "select count(*) from news where "+ condition;
            ps = conn.prepareStatement(sql);
            for (int i =1;i <= parmas.size();i++){
                ps.setObject(i,parmas.get(i-1));
            }
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(ps, conn);
        }
        return 0;
    }

    public List<New> queryNewPage(int start, int size, String condition, List<Object> parmas) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "select * from news where " + condition + " limit ?,?";
            System.out.println(sql);
            ps = conn.prepareStatement(sql);
            for (int i =1;i <= parmas.size();i++){
                ps.setObject(i,parmas.get(i-1));
            }
            ps.setObject(parmas.size()+1,start);
            ps.setObject(parmas.size()+2,size);
            ResultSet rs = ps.executeQuery();
            List<New> list = new ArrayList<New>();
            while(rs.next()) {
                New news = new New();
                news.setId(rs.getInt("id"));
                news.setTitle(rs.getString("title"));
                news.setCover(rs.getString("cover"));
                news.setContent(rs.getString("content"));
                news.setAuthor(rs.getString("author"));
                if (rs.getTimestamp("put_time") != null) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    news.setPutTime(sdf.format(rs.getTimestamp("put_time")));
                }
                list.add(news);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(ps, conn);
        }
        return null;
    }

    public New selectNew(Integer id) {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            String sql = "select * from news where id=" + id;
            ResultSet rs = stmt.executeQuery(sql);
            New aNew = null;
            if(rs.next()) {
                aNew = new New();
                aNew.setId(rs.getInt("id"));
                aNew.setAuthor(rs.getString("author"));
                aNew.setTitle(rs.getString("title"));
                aNew.setCover(rs.getString("cover"));
                aNew.setContent(rs.getString("content"));
                aNew.setPutTime(rs.getString("put_time"));
            }
            return aNew;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(stmt, conn);
        }
        return null;
    }

    public int deleteNew(Integer id) {
        int ret = DbUtil.getInstance().execute("delete from news where id=?", new Object[]{id});
        return ret;
    }

    public int newsUpload(String title,String cover,String author,String content) {
        System.out.println("------newsUpload----");
        LocalDateTime currentTime = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String putTime = currentTime.format(formatter);
        int ret = DbUtil.getInstance().execute("insert into news(title,cover,content,author,put_time) values(?,?,?,?,?)", new Object[]{title,cover,content,author,putTime});
        return ret;
    }

    public int newsUpdate(String title,String cover,String content,Integer id) {
        System.out.println("------newsUpdate----");
        System.out.println(id);
        LocalDateTime currentTime = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String putTime = currentTime.format(formatter);
        int ret = DbUtil.getInstance().execute("update news set title=?,cover=?,content=?,put_time=? where id=?", new Object[]{title,cover,content,putTime,id});
        return ret;
    }

    private Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/demo6?useUnicode=true&characterEncoding=utf8",
                    "root", "123456");
            return conn;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    private void closeConnection(Statement stmt, Connection conn) {
        try {
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}